﻿using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using System.Windows.Forms;
using DocumentGain.model;
using DocumentGain;
using DocumentGain.dal;
using System.Configuration;
using System.Threading;
using Ican.YunPan.Services;
using Ican.YunPan.Services.GridFS;

namespace DocumentGain
{
    public partial class Leading_信访工作通用 : Form
    {
        string connstr = ConfigurationManager.AppSettings["MongoConnectionString"];
        string dbName = ConfigurationManager.AppSettings["MongoDBName"];
        string FilePath = ConfigurationManager.AppSettings["FilePath"];

        string cutTime = ConfigurationManager.AppSettings["cutTime"];
        string iscutTimebefore = ConfigurationManager.AppSettings["iscutTimebefore"];


        private delegate void SetControlTextCallback(Control o, string text);
        Service service = new Service();
        private string _allmessage = "";
        string LogDate = "";

        public Leading_信访工作通用()
        {
            InitializeComponent();
        }

        public void DataLeadingin(object o)
        {
            string TableName = "BD_119_信访工作通用";
            //string ParticipantsInfo = "";
            WriteLogFile(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "开始获取数据");

            //通过关系表获取流程类型数据
            DataTable dtFlowType = GetFlowTypeCorrelation(TableName);

            //获取所有待导入数据（主表）
            DataTable dtMain = GetMainDocument(TableName);

            WriteLogFile("需要导入的数据为：" + dtMain.Rows.Count + "条.");
            //MessageShow("需要导入的数据为：" + dtMain.Rows.Count + "条.");
            DataTable dtField = GetField(TableName);

            DataTable dtWorkflow = GetWorkflow(dtFlowType.Rows[0]["WorkflowName"].ToString(), dtFlowType.Rows[0]["WorkflowPackage"].ToString());
           
            for (int i = 0; i < dtMain.Rows.Count; i++)
            {
                //WriteLogFile("开始导入第" + i + "条数据。");
                MessageShow("开始导入第" + i + "条数据。");
                string InstanceId = Guid.NewGuid().ToString();
                string DocumentID = dtMain.Rows[i]["DocumentID"].ToString();
                DataTable UserInfo = GetUserInfoEx(DocumentID);
                string FormDataId = Guid.NewGuid().ToString();
                if (InsertTabulation(TableName, InstanceId, DocumentID, dtField, i, FormDataId) > 0)
                //if(true)
                {
                    LeadinginAuditing(DocumentID, InstanceId, dtWorkflow.Rows[0]["WorkflowDisplayName"].ToString(), UserInfo, dtFlowType, dtMain, FormDataId);
                    //Thread.Sleep(100);
                    FileSave(DocumentID, InstanceId);
                }
            }
            WriteLogFile(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "数据导入完成");
        }

        private void FileSave(string DocumentID,string InstanceId)
        {
            string error = "";
            try
            {
                string sql = @"select DocumentID,filename,filepath,SUBSTRING(filepath,8,len(filepath)-1) as filepathex from [dbo].[File]
where DocumentID in (
select DocumentID from [dbo].[MainDocument]
where DocumentID = '" + DocumentID + "')";
                DataTable dtfileinfo = sqlHelp.Query(sql).Tables[0];


                string sqlvalues = @"select value from [dbo].[Tabulation]
where  cname = '内容' and DocumentID = '" + DocumentID + "'";
                DataTable dtvalues = sqlHelp.Query(sqlvalues).Tables[0];

                for (int i = 0; i < dtfileinfo.Rows.Count; i++)
                {
                    if (dtvalues.Rows.Count > 0)
                    {
                        if (!(dtvalues.Rows[0]["value"].ToString().IndexOf(dtfileinfo.Rows[i]["filename"].ToString()) > -1))
                        {
                            IDFSHandle dfsHandle = new GridFSHandle(connstr, dbName);
                            GridFSBriefInfo fileinfo = null;
                            string fileurl = FilePath + DocumentID.ToUpper() + "_" + dtfileinfo.Rows[i]["filepathex"].ToString();
                            error = fileurl;
                            string filename = dtfileinfo.Rows[i]["filename"].ToString();
                            FileStream fs = System.IO.File.Open(fileurl, FileMode.Open, FileAccess.Read);
                            fileinfo = dfsHandle.AddFile(fs, filename, true);
                            fs.Flush();
                            fs.Close();

                            WHRT_Attachment whrt_attachment = new WHRT_Attachment();
                            whrt_attachment.Id = Guid.NewGuid();
                            whrt_attachment.pid = InstanceId;
                            whrt_attachment.fileid = fileinfo.FileId;
                            whrt_attachment.filename = fileinfo.FileName;
                            whrt_attachment.fileType = "fujian";
                            whrt_attachment.orderNo = i + 1;
                            whrt_attachment.filesize = fileinfo.Length;

                            service.AddWHRT_Attachment(whrt_attachment);

                            MessageShow("文件上传成功：" + fileurl);
                        }
                    }                
                }
            }
            catch(Exception ex)
            {
                WriteLogFile("文件上传失败：" + error);
            }
        }
        
        /// <summary>
        /// 插入表单数据
        /// </summary>
        private int InsertTabulation(string TableName, string InstanceId, string DocumentID, DataTable dtField, int SequenceNo, string FormDataId)
        {
            //WriteLogFile("开始导入表单数据：" + DocumentID);

            try
            {
                string ParticipantsInfo = "";
                string ParticipantsId = "";
                string OriginatorDeptId = "";
                string userid = "";
                string orgname = "";
                string orgid = "";

                DataTable dtTabulation = GetTabulation(DocumentID);

                for (int l = 0; l < dtTabulation.Rows.Count; l++)
                {
                    if (dtTabulation.Rows[l]["cname"].ToString() == "拟稿人")
                    {
                        ParticipantsInfo = dtTabulation.Rows[l]["value"].ToString();
                    }
                }

                ParticipantsInfo = ParticipantsInfo.Split('/')[0].Replace("CN=", "");

                if (ParticipantsInfo == "")
                {
                    ParticipantsInfo = "程亮";
                }

                DataTable dtUser = GetUserInfo(ParticipantsInfo);
                if (dtUser.Rows.Count > 0)
                {
                    ParticipantsId = dtUser.Rows[0]["UserID"].ToString();
                    OriginatorDeptId = dtUser.Rows[0]["DeptID"].ToString();
                    userid = dtUser.Rows[0]["UserID"].ToString();
                    orgname = dtUser.Rows[0]["DeptName"].ToString();
                    orgid = dtUser.Rows[0]["ParentDeptCode"].ToString();
                }


                // 导入表单数据 开始
                string sqlA = "insert into " + TableName + "(";
                string sqlB = "values(";

                for (int i = 0; i < dtField.Rows.Count; i++)
                {
                    sqlA += dtField.Rows[i]["WhrtFieldName"].ToString() + ",";

                    if (dtField.Rows[i]["WhrtFieldName"].ToString() == "Id")
                    {
                        sqlB += "'" + FormDataId + "',";
                    }
                    else
                    {
                        if (dtField.Rows[i]["DocumentFieldName"].ToString() != "")
                        {

                            for (int n = 0; n < dtTabulation.Rows.Count; n++)
                            {
                                if (dtField.Rows[i]["DocumentFieldName"].ToString().Trim() == dtTabulation.Rows[n]["cname"].ToString().Trim())
                                {
                                    if (IsInt(dtTabulation.Rows[n]["value"].ToString()))
                                    {
                                        if (dtTabulation.Rows[n]["value"].ToString() != "")
                                        {
                                            sqlB += "" + dtTabulation.Rows[n]["value"].ToString() + ",";
                                        }
                                        else
                                        {
                                            sqlB += "'',";
                                        }

                                        break;
                                    }
                                    else
                                    {
                                        if (dtField.Rows[i]["DocumentFieldName"].ToString() == "拟稿人")
                                        {
                                            sqlB += "'" + dtTabulation.Rows[n]["value"].ToString().Split('/')[0].Replace("CN=", "") + "',";
                                        }
                                        else if (dtTabulation.Rows[n]["value"].ToString() != "")
                                        {
                                            sqlB += "'" + dtTabulation.Rows[n]["value"].ToString() + "',";
                                        }
                                        else
                                        {
                                            sqlB += "'',";
                                        }

                                        break;
                                    }
                                }
                                else
                                {
                                    if (n == dtTabulation.Rows.Count - 1)
                                    {
                                        if (dtField.Rows[i]["DocumentFieldName"].ToString() == "归属部门")
                                        {
                                            sqlB += "'" + orgname + "',";
                                        }
                                        if (dtField.Rows[i]["DocumentFieldName"].ToString() == "时间")
                                        {
                                            sqlB += "'',";
                                        }
                                    }
                                }
                            }
                        }

                        if (dtField.Rows[i]["DefaultValues"].ToString() != "")
                        {
                            if (IsInt(dtField.Rows[i]["DefaultValues"].ToString()))
                            {
                                sqlB += "" + dtField.Rows[i]["DefaultValues"].ToString() + ",";
                            }
                            else
                            {
                                sqlB += "'" + dtField.Rows[i]["DefaultValues"].ToString() + "',";
                            }
                        }

                        if (dtField.Rows[i]["DocumentFieldName"].ToString() == "" && dtField.Rows[i]["DefaultValues"].ToString() == "")
                        {
                            if (dtField.Rows[i]["WhrtFieldName"].ToString() == "instanceId")
                            {
                                sqlB += "'" + InstanceId + "',";
                            }

                            if (dtField.Rows[i]["WhrtFieldName"].ToString() == "workItemId")
                            {
                                sqlB += "'" + Guid.NewGuid() + "',";
                            }

                            if (dtField.Rows[i]["WhrtFieldName"].ToString() == "SequenceNo")
                            {
                                sqlB += "'" + DateTime.Now.ToString("yyyyMMddHHmmss") + SequenceNo + "',";
                            }

                            if (dtField.Rows[i]["WhrtFieldName"].ToString() == "ParticipantsId")
                            {
                                sqlB += "'" + ParticipantsId + "',";
                            }
                            if (dtField.Rows[i]["WhrtFieldName"].ToString() == "OriginatorDeptId")
                            {
                                sqlB += "'" + OriginatorDeptId + "',";
                            }
                            if (dtField.Rows[i]["WhrtFieldName"].ToString() == "userid")
                            {
                                sqlB += "'" + userid + "',";
                            }
                            if (dtField.Rows[i]["WhrtFieldName"].ToString() == "orgname")
                            {
                                sqlB += "'" + orgname + "',";
                            }
                            if (dtField.Rows[i]["WhrtFieldName"].ToString() == "orgid")
                            {
                                sqlB += "'" + orgid + "',";
                            }
                            if (dtField.Rows[i]["WhrtFieldName"].ToString() == "updatetime")
                            {
                                sqlB += "'" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "',";
                            }

                            if (dtField.Rows[i]["WhrtFieldName"].ToString() == "AttachmentIds")
                            {
                                sqlB += "'" + GetIsFile(DocumentID) + "',";
                            }

                            if (dtField.Rows[i]["WhrtFieldName"].ToString() == "拟稿人_id")
                            {
                                sqlB += "'" + userid + "',";
                            }

                            if (dtField.Rows[i]["WhrtFieldName"].ToString() == "归属部门_id")
                            {
                                sqlB += "'" + orgid + "',";
                            }

                        }
                    }
                }
                sqlA = sqlA.Substring(0, sqlA.Length - 1);
                sqlA += ")";

                sqlB = sqlB.Substring(0, sqlB.Length - 1);
                sqlB += ")";

                string sql = sqlA + " " + sqlB;

                int s = sqlHelpIcanYunPan.ExecuteSql(sql);

                if (s > 0)
                {
                    //WriteLogFile(DocumentID + "表单数据导入完成");
                }
                else
                {
                    WriteLogFile(DocumentID + "表单数据导入失败");
                }

                return s;
            }
            catch(Exception ex)
            {
                WriteLogFile(DocumentID + "|"+ex.Message);
                return 0;
            }

            
            // 导入表单数据 结束
        }

        /// <summary>
        /// 插入流程数据
        /// </summary>
        /// <param name="DocumentID"></param>
        /// <param name="InstanceId"></param>
        private void LeadinginAuditing(string DocumentID, string InstanceId, string WorkflowDisplayName, DataTable UserInfo, DataTable dtFlowType, DataTable dtMain, string FormDataId)
        {
            bool IsLeading = true;

            //WriteLogFile("开始导入流程数据" + DocumentID);
            DataTable dtAuditing = GetAuditing(DocumentID);

            for (int i = 0; i < dtAuditing.Rows.Count; i++)
            {
                string WorkItemId = Guid.NewGuid().ToString();
                string SequenceNo = "DR" + DateTime.Now.ToString("yyyyMMddHHmmss") + i;
              int s1 =  InsertSF_InstanceContext(i + 1, dtAuditing.Rows[0]["date"].ToString(), dtAuditing.Rows[dtAuditing.Rows.Count - 1]["date"].ToString(), WorkflowDisplayName, UserInfo, dtFlowType, SequenceNo);
              int s2 = InsertSF_WorkItem(WorkItemId, i + 1, dtAuditing.Rows[i]["status"].ToString(), dtAuditing.Rows[0]["date"].ToString(), dtAuditing.Rows[dtAuditing.Rows.Count - 1]["date"].ToString(), dtAuditing.Rows[i]["opinion"].ToString(), UserInfo, InstanceId, dtAuditing.Rows[i]["User"].ToString(), dtFlowType);
              int s3 = InsertSF_Token(i + 1, dtAuditing.Rows[i]["status"].ToString(), dtAuditing.Rows[0]["date"].ToString(), dtAuditing.Rows[dtAuditing.Rows.Count - 1]["date"].ToString(), InstanceId);
              int s4 = InsertMP_OpinionAttachment(InstanceId, WorkItemId, dtAuditing.Rows[i]["status"].ToString(), dtAuditing.Rows[i]["opinion"].ToString(), dtAuditing.Rows[i]["User"].ToString(), dtAuditing.Rows[i]["date"].ToString());
              int s5 = InsertMP_Flow(InstanceId, dtAuditing.Rows[i]["status"].ToString(), dtAuditing.Rows[i]["User"].ToString(), dtAuditing.Rows[i]["date"].ToString(), i, dtAuditing);
              int s6 = InsertMP_Message(dtMain.Rows[0]["flowtype"].ToString(), dtAuditing.Rows[i]["User"].ToString(), InstanceId, dtFlowType, DocumentID, dtAuditing.Rows[i]["date"].ToString(), WorkItemId, SequenceNo,FormDataId);

              if (s1 > 0)
              {
                  //WriteLogFile("SF_InstanceContext表插入成功" + DocumentID);
              }
              else
              {
                  WriteLogFile("SF_InstanceContext表插入失败" + DocumentID);
                  IsLeading = false;
              }

              if (s2 > 0)
              {
                  //WriteLogFile("SF_WorkItem表插入成功" + DocumentID);
              }
              else
              {
                  WriteLogFile("SF_WorkItem表插入失败" + DocumentID);
                  IsLeading = false;
              }

              if (s3 > 0)
              {
                  //WriteLogFile("SF_Token表插入成功" + DocumentID);
              }
              else
              {
                  WriteLogFile("SF_Token表插入失败" + DocumentID);
                  IsLeading = false;
              }

              if (s4 > 0)
              {
                  //WriteLogFile("MP_OpinionAttachment表插入成功" + DocumentID);
              }
              else
              {
                  WriteLogFile("MP_OpinionAttachment表插入失败" + DocumentID);
                  IsLeading = false;
              }

              if (s5 > 0)
              {
                  //WriteLogFile("MP_Flow表插入成功" + DocumentID);
              }
              else
              {
                  WriteLogFile("MP_Flow表插入失败" + DocumentID);
                  IsLeading = false;
              }

              if (s6 > 0)
              {
                  //WriteLogFile("MP_Message表插入成功" + DocumentID);
              }
              else
              {
                  WriteLogFile("MP_Message表插入失败" + DocumentID);
                  IsLeading = false;
              }
            }

            if (IsLeading)
            {
                UpdateMainDocument(DocumentID);
            }
            //WriteLogFile(DocumentID + "流程数据导入结束");

        }

        private void UpdateMainDocument(string DocumentID)
        {
            string sql = "update MainDocument set IsLeading = 1 where DocumentID = '" + DocumentID + "'";
            sqlHelp.ExecuteSqlEx(sql);
        }

        private int InsertMP_Message(string flowtype, string User, string InstanceId, DataTable dtFlowType, string DocumentID, string createtime, string WorkItemId, string SequenceNo, string FormDataId)
        {
            try
            {
                MP_Message mp_message = new MP_Message();
                mp_message.Id = Guid.NewGuid();
                mp_message.MsgType = "WorkFlow";
                mp_message.Status = "2";
                mp_message.Title = flowtype;
                mp_message.CurrParticipants = "[]";
                DataTable dt = GetUserInfo(User.Split('/')[0].ToString());
                if (dt.Rows.Count > 0)
                {
                    mp_message.UserId = dt.Rows[0]["UserID"].ToString();
                    mp_message.ParticipantsId = dt.Rows[0]["UserID"].ToString();
                }
                mp_message.InstanceId = InstanceId;
                mp_message.AppId = "a1ea0f79-00c4-46b3-8229-182e022da948";
                mp_message.AppName = "地铁项目";
                mp_message.MPListId = dtFlowType.Rows[0]["MPListId"].ToString();
                mp_message.FormCode = dtFlowType.Rows[0]["TableName"].ToString();
                mp_message.FormDataId = FormDataId;
                mp_message.CreatedDate = Convert.ToDateTime(createtime);
                mp_message.UpdatedDate = Convert.ToDateTime(createtime);
                mp_message.WorkItemId = WorkItemId;
                mp_message.WFStatus = 2;
                mp_message.WorkflowName = dtFlowType.Rows[0]["WorkflowName"].ToString();
                mp_message.PackageName = dtFlowType.Rows[0]["WorkflowPackage"].ToString();
                mp_message.WorkflowTitle = dtFlowType.Rows[0]["WorkflowTitle"].ToString();
                mp_message.UserName = User.Split('/')[0].ToString();
                mp_message.Contents = mp_message.UserName + "在" + mp_message.CreatedDate + "发起了【" + mp_message.WorkflowTitle + "】的流程，流程编号为：【" + SequenceNo + "】";
                return service.AddMessage(mp_message);
            }
            catch(Exception ex)
            {
                WriteLogFile(ex.Message);
                return 0;
            }
            
        }

        private int InsertMP_Flow(string InstanceId, string status, string User, string datetime, int Num, DataTable dtAuditing)
        {
            try
            {
                MP_Flow mp_flow = new MP_Flow();
                mp_flow.Id = Guid.NewGuid();
                mp_flow.InstanceId = InstanceId;
                mp_flow.CurrentActionOption = "";
                mp_flow.NextAction = "";
                mp_flow.NextActionType = "";
                mp_flow.Type = "";
                DataTable dt = GetUserInfo(User.Split('/')[0].ToString());
                if (dt.Rows.Count > 0)
                {
                    mp_flow.UserId = dt.Rows[0]["UserID"].ToString();
                    mp_flow.CurrentParticipant = dt.Rows[0]["UserName"].ToString();
                }

                mp_flow.CreateDate = Convert.ToDateTime(datetime);

                if (status == "起草")
                {
                    mp_flow.CurrentAction = status;
                    mp_flow.CurrentNode = "";
                }
                else
                {
                    mp_flow.CurrentNode = status;
                    mp_flow.CurrentAction = "";
                }

                if (Num == dtAuditing.Rows.Count - 1)
                {
                    mp_flow.NextNode = "";
                    mp_flow.NextParticipants = "";
                }
                else
                {
                    mp_flow.NextNode = dtAuditing.Rows[Num + 1]["status"].ToString();
                    mp_flow.NextParticipants = dtAuditing.Rows[Num + 1]["User"].ToString().Split('/')[0];
                }


                return service.AddFlow(mp_flow);
            }
            catch(Exception ex)
            {
                WriteLogFile(ex.Message);
                return 0;
            }

            
        }

        private int InsertMP_OpinionAttachment(string InstanceId, string WorkItemId, string status,string opinion,string User,string datetime)
        {
            try
            {
                MP_OpinionAttachment mp_opinionattachment = new MP_OpinionAttachment();
                mp_opinionattachment.Id = Guid.NewGuid();
                mp_opinionattachment.InstanceId = InstanceId;
                mp_opinionattachment.WorkItemId = WorkItemId;

                if (status.IndexOf("：") > -1)
                {
                    mp_opinionattachment.Activity = status.Split('：')[0];
                    mp_opinionattachment.Action = status.Split('：')[1];
                }
                else
                {
                    mp_opinionattachment.Activity = status;
                }
                mp_opinionattachment.Text = opinion.Replace("NoAttitude_Handler", "");

                DataTable dt = GetUserInfo(User.Split('/')[0].ToString());
                if (dt.Rows.Count > 0)
                {
                    mp_opinionattachment.UserId = dt.Rows[0]["UserID"].ToString();
                    mp_opinionattachment.ModifiedBy = dt.Rows[0]["UserName"].ToString();
                }
                mp_opinionattachment.CreatedTime = Convert.ToDateTime(datetime);
                mp_opinionattachment.State = "提交";
                return service.AddOpinionAttachment(mp_opinionattachment);
            }
            catch(Exception ex)
            {
                WriteLogFile(ex.Message);
                return 0;
            }
            
        }

        private int InsertSF_Token(int TokenId,string status,string begintime,string lasttime,string InstanceId)
        {
            try
            {
                SF_Token sf_token = new SF_Token();
                sf_token.ObjectID = Guid.NewGuid().ToString();
                sf_token.Activity = status;
                sf_token.Approval = 0;
                sf_token.CreatedTime = Convert.ToDateTime(begintime);
                sf_token.CustomCost1 = 0;
                sf_token.CustomCost2 = 0;
                sf_token.CustomCost3 = 0;
                sf_token.CustomCost4 = 0;
                sf_token.CustomCost5 = 0;
                sf_token.CustomCost1Rate = 0;
                sf_token.CustomCost2Rate = 0;
                sf_token.CustomCost3Rate = 0;
                sf_token.CustomCost4Rate = 0;
                sf_token.CustomCost5Rate = 0;
                sf_token.Dropped = 0;
                sf_token.Exceptional = 0;
                sf_token.FinishedTime = Convert.ToDateTime(lasttime);
                sf_token.HumanCost = 0;
                sf_token.HumanCostRate = 0;
                sf_token.InstanceId = InstanceId;
                sf_token.MachineCost = 0;
                sf_token.MachineCostRate = 0;
                sf_token.ManagementCost = 0;
                sf_token.ManagementCostRate = 0;
                sf_token.MaterialCost = 0;
                sf_token.MaterialCostRate = 0;
                sf_token.Retrievable = 1;
                sf_token.State = 1;
                sf_token.TokenId = TokenId;
                if ((TokenId - 1) == 0)
                {
                    sf_token.StrPreTokens = "";
                }
                else
                {
                    sf_token.StrPreTokens = (TokenId - 1).ToString() + ";";
                }
                sf_token.TotalCost = 0;
                sf_token.TotalCostRate = 0;
                sf_token.UsedTime = 0;
                sf_token.UsedTimeRate = 0;
                sf_token.ParentIndex = 0;

                return service.AddToken(sf_token);
            }
            catch(Exception ex)
            {
                WriteLogFile(ex.Message);
                return 0;
            }

            
        }

        private int InsertSF_WorkItem(string WorkItemId, int TokenId, string status, string begintime, string lastdatetime, string opinion, DataTable UserInfo, string InstanceId, string ParticipantUserName, DataTable dtFlowType)
        {
            try
            {
                SF_WorkItem sf_workitem = new SF_WorkItem();
                sf_workitem.ObjectID = WorkItemId;
                sf_workitem.ActivityAssisted = 0;
                sf_workitem.ActivityConsulted = 0;
                sf_workitem.ActionName = status;
                sf_workitem.ActivityName = status;
                sf_workitem.Approval = -1;
                sf_workitem.AssistantFinished = 0;
                sf_workitem.Assisted = 0;
                sf_workitem.BatchProcessing = 0;
                sf_workitem.CancelTime = Convert.ToDateTime(lastdatetime);
                sf_workitem.ColorArgb = 0;
                sf_workitem.Comment = opinion.Replace("NoAttitude_Handler", "");
                sf_workitem.ConsultantFinished = 1;
                sf_workitem.Consulted = 0;
                sf_workitem.DisplayName = status;
                sf_workitem.EmailApprove = 0;
                sf_workitem.FinishAccessPoint = 0;
                sf_workitem.FinishTime = Convert.ToDateTime(lastdatetime);
                sf_workitem.InstanceId = InstanceId;
                sf_workitem.ItemType = 2;
                sf_workitem.Locked = 0;
                sf_workitem.Locking = 0;
                sf_workitem.LongPermittedActions = 0;
                sf_workitem.LongUsedTime = 0;
                sf_workitem.LongWaitTime = 0;
                sf_workitem.MonitorAssistant = 0;
                sf_workitem.MonitorConsultant = 0;
                sf_workitem.NotifyFinish = 1;
                DataTable dt = GetUserInfo(ParticipantUserName.Split('/')[0].ToString());
                if (dt.Rows.Count > 0)
                {
                    sf_workitem.Participant = dt.Rows[0]["UserID"].ToString();
                    sf_workitem.Finisher = dt.Rows[0]["UserID"].ToString();
                    sf_workitem.OrgUnit = dt.Rows[0]["DeptID"].ToString();
                    sf_workitem.Originator = dt.Rows[0]["UserID"].ToString();
                }
                sf_workitem.PlanFinishTime = Convert.ToDateTime("9999-12-31 23:59:59.000");
                sf_workitem.PlanUseTime = 0;
                sf_workitem.Priority = 1;
                sf_workitem.ReceiveTime = Convert.ToDateTime(lastdatetime);
                sf_workitem.ReplyID = 0;
                sf_workitem.SeqNo = GetMaxSeqNo();
                sf_workitem.SheetType = 0;
                sf_workitem.SMSApprove = 0;
                sf_workitem.SpsTaskBounded = 0;
                sf_workitem.SpsTaskListItemId = 0;
                sf_workitem.StartTime = Convert.ToDateTime(begintime);
                sf_workitem.State = 2;
                sf_workitem.TokenId = TokenId;
                sf_workitem.Urged = 0;
                sf_workitem.WorkflowName = dtFlowType.Rows[0]["WorkflowName"].ToString();
                sf_workitem.WorkflowPackage = dtFlowType.Rows[0]["WorkflowPackage"].ToString();
                sf_workitem.WorkflowVersion = Convert.ToInt32(dtFlowType.Rows[dtFlowType.Rows.Count - 1]["WorkflowVersion"].ToString());
                sf_workitem.ParentIndex = 0;
                return service.AddWorkItem(sf_workitem);
            }
            catch(Exception ex)
            {
                WriteLogFile(ex.Message);
                return 0;
            }

            

        }

        private int InsertSF_InstanceContext(long TokenId, string begindatetime, string lastdatetime, string WorkflowDisplayName, DataTable UserInfo, DataTable dtFlowType, string SequenceNo)
        {
            try
            {
                SF_InstanceContext sf_instancecontext = new SF_InstanceContext();
                sf_instancecontext.ObjectID = Guid.NewGuid().ToString();
                sf_instancecontext.Approval = -1;
                sf_instancecontext.BinParameterTable = "0x0001000000FFFFFFFF01000000000000000401000000E20153797374656D2E436F6C6C656374696F6E732E47656E657269632E44696374696F6E61727960325B5B53797374656D2E537472696E672C206D73636F726C69622C2056657273696F6E3D342E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038395D2C5B53797374656D2E4F626A6563742C206D73636F726C69622C2056657273696F6E3D342E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038395D5D030000000756657273696F6E08436F6D7061726572084861736853697A6500030008920153797374656D2E436F6C6C656374696F6E732E47656E657269632E47656E65726963457175616C697479436F6D706172657260315B5B53797374656D2E537472696E672C206D73636F726C69622C2056657273696F6E3D342E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038395D5D08000000000902000000000000000402000000920153797374656D2E436F6C6C656374696F6E732E47656E657269632E47656E65726963457175616C697479436F6D706172657260315B5B53797374656D2E537472696E672C206D73636F726C69622C2056657273696F6E3D342E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038395D5D000000000B";
                sf_instancecontext.CreatedTime = Convert.ToDateTime(begindatetime);
                sf_instancecontext.CurrentWaitingReplyID = 1;
                sf_instancecontext.CustomCost1 = 0;
                sf_instancecontext.CustomCost1Rate = 0;
                sf_instancecontext.CustomCost2 = 0;
                sf_instancecontext.CustomCost2Rate = 0;
                sf_instancecontext.CustomCost3 = 0;
                sf_instancecontext.CustomCost3Rate = 0;
                sf_instancecontext.CustomCost4 = 0;
                sf_instancecontext.CustomCost4Rate = 0;
                sf_instancecontext.CustomCost5 = 0;
                sf_instancecontext.CustomCost5Rate = 0;
                sf_instancecontext.EstimatedFinishTime = Convert.ToDateTime(lastdatetime);
                sf_instancecontext.Exceptional = 0;
                sf_instancecontext.FinalTokenId = TokenId;
                sf_instancecontext.FinishTime = Convert.ToDateTime(lastdatetime);
                sf_instancecontext.HumanCost = 0;
                sf_instancecontext.HumanCostRate = 0;
                sf_instancecontext.InitiativeTokenId = 1;
                sf_instancecontext.InstanceName = WorkflowDisplayName;
                sf_instancecontext.IsChildInstance = 0;
                sf_instancecontext.LastActiveTime = Convert.ToDateTime(lastdatetime);
                sf_instancecontext.MachineCost = 0;
                sf_instancecontext.MaterialCostRate = 0;
                sf_instancecontext.ManagementCost = 0;
                sf_instancecontext.ManagementCostRate = 0;
                sf_instancecontext.MaterialCost = 0;
                sf_instancecontext.MaterialCostRate = 0;
                sf_instancecontext.MessageEmergency = 2;
                sf_instancecontext.NextTokenId = TokenId + 1;
                sf_instancecontext.NotifyParentFinished = 0;
                if (UserInfo.Rows.Count > 0)
                {
                    sf_instancecontext.OrgUnit = UserInfo.Rows[0]["DeptID"].ToString();
                    sf_instancecontext.Originator = UserInfo.Rows[0]["UserID"].ToString();
                }
                sf_instancecontext.ParentActivityReplyID = 0;
                sf_instancecontext.ParentActivityTokenId = -1;
                sf_instancecontext.PlanFinishTime = Convert.ToDateTime(lastdatetime);
                sf_instancecontext.Priority = 1;
                sf_instancecontext.Progress = 1;
                sf_instancecontext.Recurrences = 0;
                sf_instancecontext.RecurrencesRate = 0;
                sf_instancecontext.SequenceNo = SequenceNo;
                sf_instancecontext.SpsBounded = 0;
                sf_instancecontext.SpsInstanceListItemId = 0;
                sf_instancecontext.SpsListItemId = 0;
                sf_instancecontext.StartTime = Convert.ToDateTime(begindatetime);
                sf_instancecontext.State = 2;
                sf_instancecontext.Suspended = 0;
                sf_instancecontext.TotalCost = 0;
                sf_instancecontext.TotalCostRate = 0;
                sf_instancecontext.UsedTime = 0;
                sf_instancecontext.UserSuspended = 0;
                sf_instancecontext.WorkflowName = dtFlowType.Rows[0]["WorkflowName"].ToString();
                sf_instancecontext.WorkflowPackage = dtFlowType.Rows[0]["WorkflowPackage"].ToString();
                sf_instancecontext.WorkflowVersion = Convert.ToInt32(dtFlowType.Rows[dtFlowType.Rows.Count - 1]["WorkflowVersion"].ToString());
                sf_instancecontext.ExtField_I1 = 0;
                sf_instancecontext.ExtField_I2 = 0;
                sf_instancecontext.ExtField_I3 = 0;
                sf_instancecontext.ExtField_I4 = 0;
                sf_instancecontext.ExtField_I5 = 0;
                sf_instancecontext.ExtField_L1 = 0;
                sf_instancecontext.ExtField_L2 = 0;
                sf_instancecontext.ParentIndex = 0;
                int s = service.AddInstanceContext(sf_instancecontext);
                return s;
            }
            catch (Exception ex)
            {
                WriteLogFile(ex.Message);
                return 0;
            }
            
        }

        private DataTable GetUserInfoEx(string DocumentID)
        {
            string ParticipantsInfo = "";

            DataTable dtTabulation = GetTabulation(DocumentID);

            for (int l = 0; l < dtTabulation.Rows.Count; l++)
            {
                if (dtTabulation.Rows[l]["cname"].ToString() == "拟稿人")
                {
                    ParticipantsInfo = dtTabulation.Rows[l]["value"].ToString();
                }
            }

            ParticipantsInfo = ParticipantsInfo.Split('/')[0];

            if (ParticipantsInfo == "")
            {
                ParticipantsInfo = "程亮";
            }

            DataTable dtUser = GetUserInfo(ParticipantsInfo);
            return dtUser;
        }

        private DataTable GetWorkflow(string WorkflowName, string WorkflowPackageName)
        {
            string sql = @"select [ObjectID],[WorkflowDisplayName],[WorkflowVersion],[WorkflowName],[WorkflowPackageName] from SF_Workflow
where WorkflowName = '" + WorkflowName + "' and WorkflowPackageName = '" + WorkflowPackageName + "' order by WorkflowVersion desc";
            return sqlHelpEntApp.Query(sql).Tables[0];       
        }

        private DataTable GetAuditing(string DocumentID)
        {
            string sql = @"SELECT [AuditingID],[DocumentID],[User],[date],[opinion],[status],[num] FROM [dbo].[Auditing]
where DocumentID = '"+DocumentID+"' order by date";
            return sqlHelp.Query(sql).Tables[0];
        }

        public static bool IsInt(string value)
        {
            return Regex.IsMatch(value, @"^[+]?\d*$");
        }

        private string GetIsFile(string DocumentID)
        {
            string sql = @"select count(0) from [Document].[dbo].[File]
where DocumentID = '" + DocumentID + "'";
            int rowCount = Convert.ToInt32(sqlHelp.Query(sql).Tables[0].Rows[0][0].ToString());
            if (rowCount == 0)
            {
                return "0";
            }
            else
            {
                return "1";
            }
        }

        /// <summary>
        /// 根据对应的表名获取慧点所有流程类型及相关信息
        /// </summary>
        /// <param name="TableName"></param>
        /// <returns></returns>
        private DataTable GetFlowTypeCorrelation(string TableName)
        {
            string sql = "SELECT [ID],[flowtype],[TableName],[WorkflowName],[WorkflowPackage],[WorkflowVersion],[WorkflowTitle],[MPListId] FROM [dbo].[FlowTypeCorrelation] Where TableName = '" + TableName + "'";
            return sqlHelp.Query(sql).Tables[0];
        }

        /// <summary>
        /// 根据对应的表名和流程类型获取相关信息
        /// </summary>
        /// <param name="TableName"></param>
        /// <param name="flowtype"></param>
        /// <returns></returns>
        private DataTable GetFlowTypeCorrelation(string TableName, string flowtype)
        {
            string sql = "SELECT [ID],[flowtype],[TableName],[WorkflowName],[WorkflowPackage],[WorkflowVersion],[WorkflowTitle],[MPListId] FROM [dbo].[FlowTypeCorrelation] Where TableName = '" + TableName + "' and flowtype = '" + flowtype + "'";
            return sqlHelp.Query(sql).Tables[0];
        }

        /// <summary>
        /// 根据类型获取
        /// </summary>
        /// <param name="TableName"></param>
        /// <returns></returns>
        private DataTable GetMainDocument(string TableName)
        {
            string sql = @"SELECT [DocumentID],[dbpath],[qicaorq],[flowtype],[DocumentType] FROM [dbo].[MainDocument]
where flowtype in (
select flowtype from [Document].[dbo].[FlowTypeCorrelation]
where TableName = '" + TableName + "') and IsLeading = 0 and DocumentType = 0";

//            string sql = @"SELECT [DocumentID],[dbpath],[qicaorq],[flowtype],[DocumentType] FROM [dbo].[MainDocument]
//where flowtype in (
//select flowtype from [Document].[dbo].[FlowTypeCorrelation]
//where TableName = '" + TableName + "') and  [DocumentID] = 'FF3B5651AE84F05448257ECE001450DB'";

            //if (iscutTimebefore == "0")
            //{
            //    sql += " and qicaorq > '" + cutTime + "'";
            //}
            //else
            //{
            //    sql += " and qicaorq < '" + cutTime + "'";
            //}

            return sqlHelp.Query(sql).Tables[0];
        }

        private DataTable GetField(string TableName)
        {
            string sql = @"SELECT [ID],[flowtype],[TableName],[DocumentFieldName],[WhrtFieldName],[DefaultValues] FROM [dbo].[FieldCorrelation]
where TableName = '" + TableName + "' or TableName = '通用' ";
            return sqlHelp.Query(sql).Tables[0];
        }

        private DataTable GetTabulation(string DocumentID)
        {
            string sql = @"select [TabulationID],[DocumentID],[name],[cname],[value] from [dbo].[Tabulation]
where DocumentID = '" + DocumentID + "' ";
            return sqlHelp.Query(sql).Tables[0];
        }

        private DataTable GetUserInfo(string UserName)
        {
            if (UserName == "admin")
            {
                DataTable dtuserinfo = new DataTable();
                DataColumn dc = null;
                dc = dtuserinfo.Columns.Add("UserID", Type.GetType("System.String"));
                dc = dtuserinfo.Columns.Add("UserName", Type.GetType("System.String"));
                dc = dtuserinfo.Columns.Add("DeptID", Type.GetType("System.String"));
                dc = dtuserinfo.Columns.Add("DeptName", Type.GetType("System.String"));
                dc = dtuserinfo.Columns.Add("ParentDeptCode", Type.GetType("System.String"));

                DataRow newRow;
                newRow = dtuserinfo.NewRow();
                newRow["UserID"] = "769955F5-2B25-47AA-8DD4-69A375146359";
                newRow["UserName"] = "superadmin";
                newRow["DeptID"] = "927fea30-9944-4d40-8efc-8cb89ce7a622";
                newRow["DeptName"] = "武汉地铁";
                newRow["ParentDeptCode"] = "";
                dtuserinfo.Rows.Add(newRow);
                return dtuserinfo;
            }
            else
            {
                try
                {
                    string sqluser = "select * from UserCorrelation where UserName = '" + UserName + "'";
                    DataTable dt = sqlHelp.Query(sqluser).Tables[0];

                    string sql = @"select a.[ObjectID] as UserID,a.[Name] as UserName,b.[ObjectID] as DeptID,b.Name as DeptName,a.ParentDeptCode
from [SF_User] a inner join [SF_OrganizationUnit] b on a.ParentID = b.[ObjectID]
where a.Alias = '" + dt.Rows[0]["LoginName"].ToString() + "'";
                    return sqlHelpEntApp.Query(sql).Tables[0];

                }
                catch
                {
                    string sql = @"select a.[ObjectID] as UserID,a.[Name] as UserName,b.[ObjectID] as DeptID,b.Name as DeptName,a.ParentDeptCode
from [SF_User] a inner join [SF_OrganizationUnit] b on a.ParentID = b.[ObjectID]
where a.Name = '" + UserName + "'";
                    return sqlHelpEntApp.Query(sql).Tables[0];
                }

            }
        }

        public void MessageShow(String input)
        {
            SetControlText(this.textBox1, input);
        }

        //写入文本日志
        public void WriteLogFile(String input)
        {
            SetControlText(this.textBox1, input);

            //指定日志文件的目录
            string fname = ConfigurationManager.AppSettings["LogPath"];

            //定义文件信息对象 
            FileInfo finfo = new FileInfo(fname + "Leading_News_LogInfo" + DateTime.Now.ToString("yyyyMMdd") + ".txt");

            //判断文件是否存在以及是否大于2K 
            if (finfo.Exists && finfo.Length > 20480000)
            {
                //删除该文件 
                finfo.Delete();
            }
            //创建只写文件流 
            using (FileStream fs = finfo.OpenWrite())
            {
                //根据上面创建的文件流创建写数据流 
                StreamWriter w = new StreamWriter(fs);
                //设置写数据流的起始位置为文件流的末尾 
                w.BaseStream.Seek(0, SeekOrigin.End);

                w.Write("\r\n ");

                //写入日志内容并换行 
                w.Write(input + "\n ");
                //w.Write("--\n ");
                //清空缓冲区内容，并把缓冲区内容写入基础流 
                w.Flush();
                //关闭写数据流 
                w.Close();
            }
        }
        private void SetControlText(Control o, string text)
        {
            try
            {
                if (o.InvokeRequired)
                {
                    SetControlTextCallback d = new SetControlTextCallback(SetControlText);
                    this.Invoke(d, o, text);
                }
                else
                {
                    _allmessage = text + "\r\n" + _allmessage;
                    o.Text = _allmessage;
                }
            }
            catch { }
        }

        private int GetMaxSeqNo()
        {
            try
            {
                string sql = "select max(SeqNo)from SF_WorkItem";
                return Convert.ToInt32(sqlHelpEntApp.Query(sql).Tables[0].Rows[0][0].ToString());
            }
            catch
            {
                return 10001;
            }

        }

        private void button1_Click_1(object sender, EventArgs e)
        {
            ThreadPool.QueueUserWorkItem(DataLeadingin);
        }
    }
}
